SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[NewDiffs] AS
BEGIN
DECLARE @Comp VARCHAR(500) 
SET @Comp = 'All'

; WITH ActiveValcomInstrumentIds AS(
		SELECT DISTINCT [a].[InstrumentId] FROM [FSFundModel_VALCOMM].[dbo].[Asset] a WHERE [a].[IsActiveEndOfDay] =1
)
, ActiveLiveInstrumentIds AS ( 

		SELECT DISTINCT [a].[InstrumentId] FROM [FSFundModel].[dbo].[Asset] a WHERE [a].[IsActiveEndOfDay] =1
), Isect AS (

SELECT * FROM  [ActiveLiveInstrumentIds] 
INTERSECT				
SELECT * FROM  [ActiveValcomInstrumentIds]
), Data AS (

SELECT 
     [i].[ExtId] 
		,[i].[InstrName]
		, COALESCE(NULLIF([i].[Spread],0),[i].[FixedRate]) AS LiveRate
		, COALESCE(NULLIF([vIns].[Spread],0),[vIns].[FixedRate]) AS ValcommRate 
		, [i].[Floor] AS LiveFloor
		, [vIns].[Floor] AS ValCommFloor
		, [i].[MaturityDate] AS LiveMaturity
		, [vIns].[MaturityDate] AS ValcommMaturity
		, [i].[FaceLevThruTrn] AS LiveFaceLev
		, [vIns].[FaceLevThruTrn] AS ValcommFaceLev
		, [iss].[IssuerName] AS LiveIssuerName
		, [vIss].[IssuerName] AS ValcommIssuerName
		, [iss].[TTMEBITDA] AS LiveEBITDA
		, [vIss].[TTMEBITDA] AS ValcommEBITDA
		, [iss].[SubSector] AS LiveSubSector
		, [vIss].[SubSector] AS ValcommSubSector
		, [iss].[IntCov] AS LiveIntCov
		, [vIss].[IntCov] AS ValcommIntCov
		 
		
 FROM  [FSFundModel].[dbo].[Instrument] i 
 INNER JOIN  [FSFundModel].[dbo].[Issuer] iss ON [iss].[IssuerId] = [i].[IssuerId]
 INNER JOIN  [FSFundModel_VALCOMM].[dbo].[Instrument] vIns ON [vIns].[InstrumentId] = [i].[InstrumentId]
 INNER JOIN  [FSFundModel_VALCOMM].[dbo].[Issuer] vIss ON [vIss].[IssuerId] = [iss].[IssuerId]
 INNER JOIN [Isect] isc ON [isc].[InstrumentId] = [i].[InstrumentId] AND [vIns].[InstrumentId] = [isc].[InstrumentId]
 ),
 Compare AS (

 SELECT 		[ExtId]
 , [InstrName]
 , [LiveRate]
 , [ValcommRate]
 , [LiveFloor]
 , [ValCommFloor]
 , [LiveMaturity]
 , [ValcommMaturity]
 , [LiveFaceLev]
 , [ValcommFaceLev]
 , [LiveIssuerName]
 , [ValcommIssuerName]
 , [LiveEBITDA]
 , [ValcommEBITDA]
 , [LiveSubSector]
 , [ValcommSubSector]
 , [LiveIntCov]
 , [ValcommIntCov]

 , CASE WHEN [LiveRate] <> [ValcommRate] THEN 1 ELSE 0 END AS RateDiff
 , CASE WHEN [data].[LiveFloor] <> [ValcommFloor] THEN 1 ELSE 0 END  AS FloorDiff
 , CASE WHEN  [LiveMaturity] <> [ValcommMaturity] THEN 1 ELSE 0 END AS MaturityDiff
 , CASE WHEN [LiveFaceLev] <> [ValcommFaceLev] THEN 1 ELSE 0 END AS FaceLevDiff
 , CASE WHEN  [LiveIssuerName] <> [ValcommIssuerName] THEN 1 ELSE 0 END AS IssuerNameDiff
 , CASE WHEN [LiveEBITDA] <> [ValcommEBITDA] THEN 1 ELSE 0 END AS EBITDADiff
 , CASE WHEN [LiveSubSector] <> [ValcommSubSector] THEN 1 ELSE 0 END AS SubSectorDiff
 , CASE WHEN [LiveIntCov] <> [ValcommIntCov] THEN 1 ELSE 0 END AS IntCovDiff
 FROM [Data]
 ), Diffs AS (

 SELECT 	[ExtId]
 , [InstrName]
 , [LiveRate]
 , [ValcommRate]
 , [LiveFloor]
 , [ValCommFloor]
 , [LiveMaturity]
 , [ValcommMaturity]
 , [LiveFaceLev]
 , [ValcommFaceLev]
 , [LiveIssuerName]
 , [ValcommIssuerName]
 , [LiveEBITDA]
 , [ValcommEBITDA]
 , [LiveSubSector]
 , [ValcommSubSector]
 , [LiveIntCov]
 , [ValcommIntCov]
 , [RateDiff]
 , [FloorDiff]
 , [MaturityDiff]
 , [FaceLevDiff]
 , [IssuerNameDiff]
 , [EBITDADiff]
 , [SubSectorDiff]
 , [IntCovDiff]
 , [RateDiff] + [FloorDiff] + [MaturityDiff] + [FaceLevDiff] + [IssuerNameDiff] + [EBITDADiff]+ [SubSectorDiff] + [IntCovDiff] AS AnyDiff

 FROM  [Compare] )

 SELECT * FROM  [Diffs]
		WHERE [AnyDiff] > 0
END

GO
